﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;

namespace CRM.UTIL
{
    public class ExcelHelper
    {
        /// 根据模板导出生成Excel
        /// 
        /// 说说模板语法：
        /// 1. &=DataSource.Field，&=[DataSource].[Field]  是对DataTable和几何类型的引用，将会从当前行开始竖直向下生成多行数据。
        /// 2. &=$data  是对变量或数组的引用。数组存在skip，horizontal等属性，具体参见官方网站
        /// 3. &=&=  动态公式计算；{r}当前行，{c}当前列，{-n}，{n}当前行或列的偏移量前n或后n。
        /// 4. &==   是动态计算，如excel，if等语句。（if（logic_test,true_value,false_value））
        /// 
        /// </summary>
        /// <param name="excelTemplatePath">模板地址</param>
        /// <param name="dicVariables">变量列表</param>
        /// <param name="dtSources">DataTable型变量列表</param>
        public static void ExportToExcel(string excelTemplatePath, Dictionary<string, object> dicVariables, List<DataTable> dtSources, string fileName = "")
        {
            var Response = HttpContext.Current.Response;
            WorkbookDesigner designer = new WorkbookDesigner();
            designer.Open(excelTemplatePath);

            if (dtSources != null)
            {
                foreach (var dt in dtSources)
                {
                    //插入空行
                    if (dt.Rows.Count == 0)
                    {
                        DataRow dr = dt.NewRow();

                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            try
                            {
                                dr[i] = " ";
                            }
                            catch { }
                        }

                        dt.Rows.Add(dr);
                    }

                    //数据源 
                    designer.SetDataSource(dt);
                }
            }

            if (dicVariables != null)
            {
                foreach (var item in dicVariables)
                {
                    designer.SetDataSource(item.Key, item.Value);
                }
            }

            designer.Process();

            if (string.IsNullOrEmpty(fileName))
            {
                fileName = DateTime.Now.ToString("yyyyMMddHHmmss");
            }
            else
            {
                fileName = HttpUtility.UrlEncode(fileName);
            }

            designer.Save(string.Format("{0}.xls", fileName), SaveType.OpenInExcel, FileFormatType.Excel2003, Response);
            Response.Flush();
            Response.Close();
            designer = null;
            Response.End();
        }

        /// <summary>
        /// 把DataTable里面的数据保存成Excel
        /// </summary>
        /// <param name="dtData">要导入的DataTable，一定需要一个表名，否则无法导入</param>
        /// <param name="filePath">保存Excel的路径</param>
        public static void DataTableExport2Excel(DataTable dtData, string filePath)
        {
            OleDbConnection OleDbConn = new OleDbConnection();
            try
            {
                //Create OleDbConn
                string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
                               + "Data Source=" + filePath + ";"
                               + "Extended Properties=\"Excel 8.0;HDR=YES\"";

                OleDbConn.ConnectionString = connStr;
                OleDbConn.Open();


                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = OleDbConn;

                //Create Table Of Excel
                string createTableStr = "Create Table [" + (dtData.TableName == "" ? "Sheet1" : dtData.TableName) + "](";
                int i, j, k;
                for (i = 0; i < dtData.Columns.Count - 1; i++)
                {
                    createTableStr += "[" + dtData.Columns[i].ColumnName + "] text,";
                }
                createTableStr += "[" + dtData.Columns[i].ColumnName + "] text)";

                cmd.CommandText = createTableStr;
                cmd.ExecuteNonQuery();

                //Save Data To Excel File
                string insertStr = "";
                for (j = 0; j < dtData.Rows.Count; j++)
                {
                    insertStr = "INSERT INTO [" + (dtData.TableName == "" ? "Sheet1" : dtData.TableName) + "$] values('";
                    for (k = 0; k < dtData.Columns.Count - 1; k++)
                    {
                        insertStr += dtData.Rows[j][k].ToString() + "','";
                    }
                    insertStr += dtData.Rows[j][k].ToString() + "')";

                    cmd.CommandText = insertStr;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                //Close OleDbConn
                OleDbConn.Close();
            }
        }

        /// <summary>
        /// 转换中文excel名称，防止乱码
        /// </summary>
        /// <param name="fileName">中文名称</param>
        /// <returns></returns>
        public static string EncodeFileName(string fileName)
        {
            string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower();
            if (UserAgent.IndexOf("firefox") == -1)
                fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
            return fileName;
        }

        public static DataTable ExcelToTable(string path)
        {
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + path + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";

            DataTable dt = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;


                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                //包含excel中表名的字符串数组
                if (dtSheetName != null)
                {
                    var strTableNames = new string[dtSheetName.Rows.Count];
                    for (int k = 0; k < dtSheetName.Rows.Count; k++)
                    {//我是倒序插入到strTableNames中，因为dtSheetName中行是从后往前读sheet页的
                        strTableNames[k] = dtSheetName.Rows[dtSheetName.Rows.Count - k - 1]["TABLE_NAME"].ToString();
                    }

                    DataSet ds = null;
                    strExcel = string.Format("select * from [{0}]", strTableNames[0]);
                }
                myCommand = new OleDbDataAdapter(strExcel, strConn);

                myCommand.Fill(dt);
            }
            return dt;

        }
    }
}
